package com.b2c.repository;

import com.b2c.entity.SupplierGoodsEntity;
import com.b2c.entity.result.PagingResponse;
import com.b2c.entity.shop.ShopTrafficGoodsEntity;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

@Repository
public class SupplierGoodsRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    public SupplierGoodsEntity insert(SupplierGoodsEntity entity) {
        String sql1="SELECT COUNT(0) FROM erp_goods_supplier WHERE supplierId=? AND `number`=?";
        Integer count = jdbcTemplate.queryForObject(sql1,Integer.class,entity.getSupplierId(),entity.getNumber());
        if(count>0) return null;
        String sql = "INSERT INTO erp_goods_supplier (supplierId,`number`,color,`size`,price,attr1,attr2,panUrl,panPwd,remark) value (?,?,?,?,?,?,?,?,?,?)";
        try {
            //插入数据库
            KeyHolder keyHolder = new GeneratedKeyHolder();
            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                    ps.setInt(1, entity.getSupplierId());
                    ps.setString(2, entity.getNumber());
                    ps.setString(3, entity.getColor());
                    ps.setString(4, entity.getSize());
                    ps.setString(5, entity.getPrice());
                    ps.setString(6, entity.getAttr1());
                    ps.setString(7, entity.getAttr2());
                    ps.setString(8, entity.getPanUrl());
                    ps.setString(9, entity.getPanPwd());
                    ps.setString(10, entity.getRemark());
                    return ps;
                }
            }, keyHolder);

            Integer id = keyHolder.getKey().intValue();
            entity.setId(id);
            return entity;
        }catch (Exception e) {
            return entity;
        }
    }

    @Transactional
    public PagingResponse<SupplierGoodsEntity> getList(Integer pageIndex, Integer pageSize, String number, Integer supplierId,Boolean publishStatus) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT SQL_CALC_FOUND_ROWS  a.*,c.`name` as supplierName,IFNULL(b.shopIds,'') AS shopIds,b.publishDate FROM  erp_goods_supplier  a ");
        sb.append(" LEFT JOIN erp_contact c on c.id=a.supplierId ");
        sb.append(" LEFT JOIN erp_goods_publish_record b on b.goodsSupplierId = a.id ");
        sb.append( " WHERE 1=1 ");

        List<Object> params = new ArrayList<>();
        if (supplierId!=null && supplierId>0) {
            sb.append(" and a.supplierId = ? ");
            params.add(supplierId);
        }
        if(StringUtils.hasText(number)){
            sb.append(" and a.`number` Like ? ");
            params.add("%"+number+"%");
        }
        if(publishStatus == null){
            //不查询发布状态
        }
        else if(publishStatus){
            //有发布
            sb.append(" and (b.shopIds IS NOT NULL AND  b.shopIds <> '' )");
        }else if(publishStatus==false){
            //未发布
            sb.append(" and (b.shopIds IS NULL OR  b.shopIds = '' )");
        }


        sb.append(" ORDER BY b.publishDate desc,b.updateTime desc LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        List<SupplierGoodsEntity> lists = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(SupplierGoodsEntity.class), params.toArray(new Object[params.size()]));
        return new PagingResponse<>(pageIndex, pageSize, getTotalSize(), lists);
    }
    protected int getTotalSize() {
        return jdbcTemplate.queryForObject("SELECT FOUND_ROWS() as row_num;", int.class);
    }

    public void updateAttr3(Integer id, String attr3) {
        jdbcTemplate.update("UPDATE erp_goods_supplier SET attr3=? WHERE id=?",attr3,id);
    }
}
